package com.axinfu.util.db;

import com.axinfu.util.EmptyUtil;
import com.axinfu.util.StringUtil;
import com.axinfu.util.db.dialect.DefaultDialect;
import com.axinfu.util.db.dialect.OracleDialect;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Predicate;

/**
 * MetaBuilder
 *
 * @author ZJN
 * @since 2022/5/20
 */
public class MetaBuilder {

    private DataSource dataSource;

    /**
     * 方言
     */
    private DefaultDialect dialect = new DefaultDialect();

    /**
     * 是否包含表
     */
    private Predicate<String> isIncludeTable = (tableName) -> true;

    /**
     * "TABLE","VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY","LOCAL TEMPORARY", "ALIAS", "SYNONYM"
     */
    private String[] types = new String[]{"TABLE"};

    /**
     * sql类型与java类型转换规则
     */
    private HandleJavaType handleJavaType = new HandleJavaType();

    private Connection conn = null;
    private DatabaseMetaData dbMeta = null;

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param dialect        dialect
     * @param isIncludeTable isSkipTable
     * @param types          types
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect, Predicate<String> isIncludeTable, String[] types,
                       HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
        this.isIncludeTable = isIncludeTable;
        this.types = types;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param dialect        dialect
     * @param isIncludeTable isSkipTable
     * @param types          types
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect, Predicate<String> isIncludeTable,
                       String[] types) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
        this.isIncludeTable = isIncludeTable;
        this.types = types;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param dialect        dialect
     * @param isIncludeTable isSkipTable
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect, Predicate<String> isIncludeTable,
                       HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
        this.isIncludeTable = isIncludeTable;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param dialect        dialect
     * @param types          types
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect, String[] types,
                       HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
        this.types = types;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param isIncludeTable isSkipTable
     * @param types          types
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, Predicate<String> isIncludeTable, String[] types,
                       HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.isIncludeTable = isIncludeTable;
        this.types = types;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param dialect        dialect
     * @param isIncludeTable isSkipTable
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect, Predicate<String> isIncludeTable) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
        this.isIncludeTable = isIncludeTable;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param dialect        dialect
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect, HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param types          types
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, String[] types,
                       HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.types = types;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource dataSource
     * @param dialect    dialect
     * @param types      types
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect, String[] types) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
        this.types = types;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param isIncludeTable isSkipTable
     * @param types          types
     */
    public MetaBuilder(DataSource dataSource, Predicate<String> isIncludeTable, String[] types) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.isIncludeTable = isIncludeTable;
        this.types = types;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param isIncludeTable isSkipTable
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, Predicate<String> isIncludeTable, HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.isIncludeTable = isIncludeTable;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param handleJavaType handleJavaType
     */
    public MetaBuilder(DataSource dataSource, HandleJavaType handleJavaType) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(handleJavaType)) {
            throw new IllegalArgumentException("handleJavaType can not be null.");
        }
        this.dataSource = dataSource;
        this.handleJavaType = handleJavaType;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource dataSource
     * @param types      types
     */
    public MetaBuilder(DataSource dataSource, String[] types) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(types)) {
            throw new IllegalArgumentException("types can not be null.");
        }
        this.dataSource = dataSource;
        this.types = types;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource     dataSource
     * @param isIncludeTable isSkipTable
     */
    public MetaBuilder(DataSource dataSource, Predicate<String> isIncludeTable) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(isIncludeTable)) {
            throw new IllegalArgumentException("isSkipTable can not be null.");
        }
        this.dataSource = dataSource;
        this.isIncludeTable = isIncludeTable;
    }


    /**
     * MetaBuilder
     *
     * @param dataSource dataSource
     * @param dialect    dialect
     */
    public MetaBuilder(DataSource dataSource, DefaultDialect dialect) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        if (EmptyUtil.isEmpty(dialect)) {
            throw new IllegalArgumentException("dialect can not be null.");
        }
        this.dataSource = dataSource;
        this.dialect = dialect;
    }

    /**
     * MetaBuilder
     *
     * @param dataSource dataSource
     */
    public MetaBuilder(DataSource dataSource) {
        if (EmptyUtil.isEmpty(dataSource)) {
            throw new IllegalArgumentException("dataSource can not be null.");
        }
        this.dataSource = dataSource;
    }

    public List<TableMeta> build() {
        try {
            conn = dataSource.getConnection();
            dbMeta = conn.getMetaData();

            List<TableMeta> tableMetas = new ArrayList<>();
            buildTableNames(tableMetas);
            for (TableMeta tableMeta : tableMetas) {
                buildPrimaryKey(tableMeta);
                buildColumnMetas(tableMeta);
            }
            return tableMetas;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeConnection();
        }
    }

    /**
     * 不同数据库 dbMeta.getTables(...) 的 schemaPattern 参数意义不同
     * 1：oracle 数据库这个参数代表 dbMeta.getUserName()
     * 2：postgresql 数据库中需要在 jdbcUrl中配置 schemaPatter，例如：
     * jdbc:postgresql://localhost:15432/djpt?currentSchema=public,sys,app
     * 最后的参数就是搜索schema的顺序，DruidPlugin 下测试成功
     * 3：开发者若在其它库中发现工作不正常，可通过继承 MetaBuilder并覆盖此方法来实现功能
     *
     * @return ResultSet
     * @throws SQLException SQLException
     */
    protected ResultSet getTablesResultSet() throws SQLException {
        String schemaPattern = dialect instanceof OracleDialect ? dbMeta.getUserName() : null;
        return dbMeta.getTables(conn.getCatalog(), schemaPattern, null, types);
    }

    protected void buildTableNames(List<TableMeta> ret) throws SQLException {
        ResultSet rs = getTablesResultSet();
        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");

            if (!isIncludeTable.test(tableName)) {
                continue;
            }

            TableMeta tableMeta = new TableMeta();
            tableMeta.setTableName(tableName);
            tableMeta.setRemarks(rs.getString("REMARKS"));

            ret.add(tableMeta);
        }
        rs.close();
    }

    /**
     * 构建主键
     *
     * @param tableMeta tableMeta
     * @throws SQLException SQLException
     */
    protected void buildPrimaryKey(TableMeta tableMeta) throws SQLException {
        Set<String> primaryKeys = new HashSet<>();
        ResultSet rs = dbMeta.getPrimaryKeys(conn.getCatalog(), null, tableMeta.getTableName());
        while (rs.next()) {
            String cn = rs.getString("COLUMN_NAME");
            primaryKeys.add(cn);
        }
        tableMeta.setPrimaryKeys(primaryKeys);
        tableMeta.setPrimaryKey(StringUtil.join(primaryKeys));
        rs.close();
    }

    /**
     * 文档参考：
     * <a href="http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html">http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html</a>
     * <p>
     * JDBC 与时间有关类型转换规则，mysql 类型到 java 类型如下对应关系：
     * DATE				java.sql.Date
     * DATETIME			java.sql.Timestamp
     * TIMESTAMP[(M)]	java.sql.Timestamp
     * TIME				java.sql.Time
     * <p>
     * 对数据库的 DATE、DATETIME、TIMESTAMP、TIME 四种类型注入 new java.util.Date()对象保存到库以后可以达到“秒精度”
     * 为了便捷性，getter、setter 方法中对上述四种字段类型采用 java.util.Date，可通过定制 TypeMapping 改变此映射规则
     *
     * @param tableMeta tableMeta
     * @throws SQLException SQLException
     */
    protected void buildColumnMetas(TableMeta tableMeta) throws SQLException {
        String sql = dialect.forTableBuilderDoBuild(tableMeta.getTableName());
        Statement stm = conn.createStatement();
        ResultSet rs = stm.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        Map<String, ColumnMeta> columnMetaMap = new HashMap<>(0);

        //获取部分属性
        try (ResultSet colMetaRs = dbMeta.getColumns(conn.getCatalog(), null, tableMeta.getTableName(), null)) {
            while (colMetaRs.next()) {
                ColumnMeta columnMeta = new ColumnMeta(tableMeta);
                columnMeta.setColumnName(colMetaRs.getString("COLUMN_NAME"));
                columnMeta.setRemarks(colMetaRs.getString("REMARKS"));
                columnMeta.setDefaultValue(colMetaRs.getString("COLUMN_DEF"));
                columnMetaMap.put(columnMeta.getColumnName(), columnMeta);
            }
        } catch (Exception e) {
            System.err.println("无法生成 REMARKS");
        }

        //获取其他
        for (int i = 1; i <= columnCount; i++) {
            ColumnMeta columnMeta = new ColumnMeta(tableMeta);
            columnMeta.setColumnName(rsmd.getColumnName(i));
            columnMeta.setColumnType(rsmd.getColumnType(i));
            columnMeta.setColumnTypeName(rsmd.getColumnTypeName(i));
            columnMeta.setColumnDisplaySize(rsmd.getColumnDisplaySize(i));
            columnMeta.setPrecision(rsmd.getPrecision(i));
            columnMeta.setScale(rsmd.getScale(i));
            columnMeta.setNullable(rsmd.isNullable(i));
            columnMeta.setAutoIncrement(rsmd.isAutoIncrement(i));
            columnMeta.setCaseSensitive(rsmd.isCaseSensitive(i));
            columnMeta.setSearchable(rsmd.isSearchable(i));
            columnMeta.setCurrency(rsmd.isCurrency(i));
            columnMeta.setSigned(rsmd.isSigned(i));
            columnMeta.setColumnLabel(rsmd.getColumnLabel(i));
            columnMeta.setIsReadOnly(rsmd.isReadOnly(i));
            columnMeta.setIsWritable(rsmd.isWritable(i));
            columnMeta.setIsDefinitelyWritable(rsmd.isDefinitelyWritable(i));

            columnMeta.setIsPrimaryKey(tableMeta.getPrimaryKey().contains(columnMeta.getColumnName()));
            columnMeta.setName(StringUtil.lineToHump(columnMeta.getColumnName()));
            columnMeta.setRemarks(columnMetaMap.get(columnMeta.getColumnName()).getRemarks());
            columnMeta.setType(
                    columnMeta.getColumnTypeName() +
                            "(" +
                            columnMeta.getColumnDisplaySize() +
                            (columnMeta.getScale() > 0 ? "," + columnMeta.getScale() : "") +
                            ")"
            );
            columnMeta.setJavaTypeName(handleJavaType.handler(rsmd, i));
            columnMeta.setJavaType(columnMeta.getJavaTypeName()
                    .substring(columnMeta.getJavaTypeName().lastIndexOf(".") + 1));
            columnMeta.setIntegerJavaType(columnMeta.getColumnType() == Types.TINYINT
                    || columnMeta.getColumnType() == Types.SMALLINT
                    || columnMeta.getColumnType() == Types.INTEGER
                    || columnMeta.getColumnType() == Types.BIGINT
            );
            columnMeta.setBigDecimalJavaType(columnMeta.getColumnType() == Types.FLOAT
                    || columnMeta.getColumnType() == Types.REAL
                    || columnMeta.getColumnType() == Types.DOUBLE
                    || columnMeta.getColumnType() == Types.DECIMAL
                    || columnMeta.getColumnType() == Types.NUMERIC);
            columnMeta.setStringJavaType(HandleJavaType.TYPE_MAPPING.get(columnMeta.getColumnType()).equals(String.class.getName()));
            columnMeta.setDateJavaType(HandleJavaType.TYPE_MAPPING.get(columnMeta.getColumnType()).equals(Date.class.getName()));
            columnMeta.setObjectJavaType(HandleJavaType.TYPE_MAPPING.get(columnMeta.getColumnType()).equals(Object.class.getName()));
            columnMeta.setBytesJavaType("byte[]".equals(HandleJavaType.TYPE_MAPPING.get(columnMeta.getColumnType())));
            if (columnMeta.isIntegerJavaType() || columnMeta.isBigDecimalJavaType()) {
                if (!columnMeta.isSigned()) {
                    columnMeta.setMin("1");
                }
                String max;
                if (columnMeta.getScale() == 0) {
                    max = StringUtil.format2length("9", columnMeta.getPrecision(), "9", 1);
                } else {
                    max = StringUtil.format2length("9", columnMeta.getPrecision(), "9", 1)
                            + StringUtil.format2length(".", columnMeta.getScale() + 1, "9", 1);
                }
                columnMeta.setMax(max);
            }
            tableMeta.getColumnMetas().add(columnMeta);
        }

        rs.close();
        stm.close();
    }

    private void closeConnection() {
        if (EmptyUtil.isNotEmpty(conn)) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public DefaultDialect getDialect() {
        return dialect;
    }

    public void setDialect(DefaultDialect dialect) {
        this.dialect = dialect;
    }

    public Predicate<String> getIsIncludeTable() {
        return isIncludeTable;
    }

    public void setIsIncludeTable(Predicate<String> isIncludeTable) {
        this.isIncludeTable = isIncludeTable;
    }

    public String[] getTypes() {
        return types;
    }

    public void setTypes(String[] types) {
        this.types = types;
    }

    public HandleJavaType getHandleJavaType() {
        return handleJavaType;
    }

    public void setHandleJavaType(HandleJavaType handleJavaType) {
        this.handleJavaType = handleJavaType;
    }
}
